<?php
namespace Home\Controller;

use Think\Controller;
class QiuBaoWebController extends BaseController {
	
    /**
     * 测试函数
     * @return [type] [description]
     */
    public function test(){
        var_dump('恭喜您，成功访问QiuBaoWebController控制器！');
    }

    //Find_Bill账单信息查找接口:查询账单信息表      
    public function Find_Bill(Type $var = null)
    {
        # code...
        // 实例化账单数据表
        $Bill = M('bill');

        //拼接POST传入过来的值，以SQL语句的形式
        $sql_type = "";
        $sql_status = "";
        $sql_year = "";

        
        if ($_POST['service_type'] != '全部' && $_POST['service_type']!=NULL){
            $sql_type = "AND goods.goods_type="."'".$_POST['service_type']."' ";
            // dump($sql_type);
        }

        if ($_POST['service_status'] != '全部' && $_POST['service_status']!=NULL){
            $sql_status = "AND bill.bill_status="."'".$_POST['service_status']."' ";
            // dump($sql_type);
        }


        if ($_POST['service_year'] != '' && strlen($_POST['service_year']) == 4){
            $sql_year = "AND bill.paid_time LIKE '%".$_POST['service_year']."%' ";
            // dump($sql_year);
        }


        //设计SQL（要联合查询bill表中的全部信息和device表中的device_name和coupon表中优惠券价值字段coupon_price）
        $sql = "SELECT bill.bill_id,device.device_name,device.device_location,users.user_phone,goods.goods_name,goods.goods_type,bill.useTime,goods.goods_price,IF(bill.coupon_name='您没有优惠卷哦~','无',bill.coupon_name) AS coupon_name,bill.use_coupon,bill.paid_time,bill.bill_status
        FROM bill,device,goods,users WHERE (bill.device_id=device.device_id AND goods.goods_id=bill.goods_id AND users.user_id=bill.user_id ".$sql_type.$sql_status.$sql_year.")";

        // dump($sql);
        //查询数据
        $all_message = $Bill->query($sql);
        // dump($all_message);
        // dump(count($all_message));

        for ($i=0; $i < count($all_message); $i++) { 
            // dump($all_message[$i]);
            $coupon_name = $all_message[$i]['coupon_name'];
            //SQL查询coupon_price
            $sql = "SELECT coupon_price FROM coupon WHERE coupon_name='$coupon_name'";
            $coupon_price = $Bill->query($sql)[0]['coupon_price'];
            // $all_message[$i]['coupon_price'] = $coupon_price;
            $all_message[$i]['bill_price'] = ($all_message[$i]['goods_price'] * $all_message[$i]['usetime']) - ($all_message[$i]['use_coupon'] * $coupon_price);
            // // dump($all_message[$i]);
        }
        
        // dump($all_message);
        $return_data = array();
        $return_data['error_code'] = 0;
        $return_data['msg'] = '数据获取成功';
        $return_data['data'] = $all_message;
        $this->ajaxReturn($return_data);
    }

    //Find_device设备信息查找接口:查询设备信息表
    public function Find_device(Type $var = null)
    {
        # code...
        // 实例化账单数据表
        $device = M('device');


        //拼接POST传入过来的值，以SQL语句的形式
        $sql_location = "";
        $sql_state = "";
        $sql_devicename = "";

        
        if ($_POST['device_location'] != '全部' && $_POST['device_location']!=NULL){
            $sql_location = "AND device_location="."'".$_POST['device_location']."' ";
        }

        if ($_POST['storage_state'] != '全部' && $_POST['storage_state']!=NULL){
            if ($_POST['storage_state']=='充足'){
                $sql_state = "AND deviceNum_drink>=10";
            } 
            
            if ($_POST['storage_state']=='紧缺'){
                $sql_state = "AND deviceNum_drink<10";
            }
        }


        // dump($_POST['device_name']);
        if ($_POST['device_name'] != ''){
            $sql_devicename = "AND device_name="."'".$_POST['device_name']."' ";
        }



        //设计SQL
        $sql = "SELECT device_id,device_name,device_location,IF(deviceNum_drink>=10,'充足','紧缺') AS storage_state FROM device WHERE (1=1 ".$sql_location.$sql_state.$sql_devicename.")";
        // dump($sql);
        //查询数据
        $all_message = $device->query($sql);
        // dump($all_message);
        // dump(count($all_message));
        
        // dump($all_message);
        $return_data = array();
        $return_data['error_code'] = 0;
        $return_data['msg'] = '数据获取成功';
        $return_data['data'] = $all_message;
        $this->ajaxReturn($return_data);
    }

    //Find_coupons优惠劵信息查找接口:查询优惠劵信息表
    public function Find_coupons(Type $var = null)
    {
        # code...
        // 实例化优惠劵数据表
        $coupon = M('coupon');

        $sql_couponname = '';

        if ($_POST['coupon_name'] != '') {
            $sql_couponname = "AND coupon_name LIKE "."'%".$_POST['coupon_name']."%'";
        }

        //设计SQL
        $sql = "SELECT coupon_id,coupon_name,coupon_type,coupon_content,coupon_price,coupon_level,coupon_time_limit FROM coupon WHERE 1=1 ".$sql_couponname;
        // dump($sql);

        //查询数据
        $all_message = $coupon->query($sql);
        // dump($all_message);
        // dump(count($all_message));
        
        // dump($all_message);
        $return_data = array();
        $return_data['error_code'] = 0;
        $return_data['msg'] = '数据获取成功';
        $return_data['data'] = $all_message;
        $this->ajaxReturn($return_data);
    }

    //Find_goods商品信息查找接口:查询商品信息表
    public function Find_goods(Type $var = null)
    {
        # code...
        // 实例化账单数据表
        $goods = M('goods');

        $sql_goodsname='';
        $sql_goodsType='';

        if($_POST['goods_name'] !='' && strlen($_POST['goods_name'])<=20 ){
            $sql_goodsname ="AND goods_name LIKE "."'%".$_POST['goods_name']."%'";
        }

        if($_POST['goods_type'] !='全部' && $_POST['goods_type']!=NULL){
            $sql_goodsType ="AND goods_type = "."'".$_POST['goods_type']."'";
        }



        //设计SQL
        $sql = "SELECT goods_id,goods_name,goods_type,goods_price FROM goods WHERE 1=1 ".$sql_goodsname.$sql_goodsType;
        // dump($sql);
        
        //查询数据
        $all_message = $goods->query($sql);
        // dump($all_message);
        // dump(count($all_message));
        
        // dump($all_message);
        $return_data = array();
        $return_data['error_code'] = 0;
        $return_data['msg'] = '数据获取成功';
        $return_data['data'] = $all_message;
        $this->ajaxReturn($return_data);
    }

    //Find_users用户信息查找接口:查询用户信息表
    public function Find_users(Type $var = null)
    {
        # code...
        // 实例化用户数据表
        $users = M('users');

        $sql_userlevel = '';
        $sql_username = '';

        if ($_POST['user_level'] != NULL && $_POST['user_level'] != '全部') {
            # code...
            if ($_POST['user_level']=='普通用户') {
                # code...
                $sql_userlevel = 'AND user_level=1 ';
            }
            if ($_POST['user_level']=='VIP用户') {
                # code...
                $sql_userlevel = 'AND user_level=0 ';
            }   
        }
        if ($_POST['user_name'] != NULL && $_POST['user_name'] != '') {
            # code...
            $sql_username = 'AND user_phone='."'".$_POST['user_name']."'";
        }

        //设计SQL
        $sql = "SELECT user_id,user_phone,IF(user_level=0,'VIP用户','普通用户') AS user_level FROM users WHERE 1=1 ".$sql_userlevel.$sql_username;
        // dump($sql);
        //查询数据
        $all_message = $users->query($sql);
        // dump($all_message);
        // dump(count($all_message));
        
        // dump($all_message);
        $return_data = array();
        $return_data['error_code'] = 0;
        $return_data['msg'] = '数据获取成功';
        $return_data['data'] = $all_message;
        $this->ajaxReturn($return_data);
    }

    //Find_Manager管理员信息查找接口:查询管理员信息表
    public function Find_Manager(Type $var = null)
    {
        # code...
        // 实例化账单数据表
        $manager = M('manager');

        $sql_managername = '';

        if ($_POST['manager_name'] != NULL && $_POST['manager_name'] != '') {
            # code...
            $sql_managername = 'AND manager_phone='."'".$_POST['manager_name']."'";
        }

        //设计SQL
        $sql = "SELECT manager_id,manager_phone,manager_device FROM manager WHERE 1=1 ".$sql_managername;

        //查询数据
        $all_message = $manager->query($sql);
        // dump($all_message);
        // dump(count($all_message));
        
        // dump($all_message);
        $return_data = array();
        $return_data['error_code'] = 0;
        $return_data['msg'] = '数据获取成功';
        $return_data['data'] = $all_message;
        $this->ajaxReturn($return_data);
    }
}
